﻿Imports DTO
Imports System.Data.OleDb

Public Class SachDAO
    Public Shared Function TimKiem(ByVal maSach As Integer)
        Dim sDTO As New SachDTO
        Dim cn As OleDbConnection
        'B1 va B2: Tao chuoi ket noi,mo ket noi bang oi tuong ket noi
        cn = DatabaseProvider.connectData()
        'B3:tao chuoi strSQL thao tac co so du lieu 
        Dim strSQL As String
        strSQL = "Select * From Sach Where Ma = ?"
        'B4:Thuc thi chuoi strSQL
        Dim cmd As New OleDbCommand(strSQL, cn)
        cmd.Parameters.Add("@Ma", OleDbType.Integer)
        cmd.Parameters("@Ma").Value = maSach
        Dim dr As OleDbDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            sDTO.MaSach = dr("MaSach")
            sDTO.TenSach = dr("TenSach")
            sDTO.TacGia = dr("TacGia")
            sDTO.TriGia = dr("TriGia")
            sDTO.NhaXuatBan = dr("NhaXuatBan")
            sDTO.NamXuatBan = dr("NamXuatBan")
            sDTO.NgayNhap = dr("NgayNhap")
            sDTO.MaTheLoai = dr("MaTheLoai")
            sDTO.MaNhanVien = dr("MaNhanVien")
            'sDTO.KhoangCachXuatBan = dr("KhoangCachXuatBan")
            sDTO.MaTinhTrang = dr("MaTinhTrang")
        End While

        'B5:Dong ket noi
        dr.Close()
        cn.Close()
        Return sDTO


    End Function


    Public Function TimKiem(ByVal sCrt As SachCrt) As DataTable
        Dim dt As New DataTable()
        Dim cn As OleDbConnection
        cn = DatabaseProvider.connectData()
        Dim cmd As OleDbCommand = BuildQuery(sCrt, cn)
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        Return dt


    End Function



    Private Function BuildQuery(ByVal sCrt As SachCrt, ByVal cn As OleDbConnection)
        Dim cmd As New OleDbCommand()
        Dim strDKMaSach = " 1=1 "
        Dim strDKTenSach = " 1=1 "
        Dim strDKTacGia = " 1=1 "
        Dim strDKNgayNhap = " 1=1 "
        Dim strDKNhaXuatBan = " 1=1 "
        Dim strDKNamXuatBan = " 1=1 "
        Dim strDKMaNhanVien = " 1=1 "
        Dim strDKMaTinhTrang = " 1=1 "
        Dim strDKKhoangCachXuatBan = " 1=1 "
        Dim strDKTriGia = " 1=1 "
        Dim strDKMaTheLoai = " 1=1 "

        If (sCrt.MaSach <> 0) Then
            strDKMaSach = "MaSach = ? "
            cmd.Parameters.Add("@Ma", OleDbType.Integer)
            cmd.Parameters("@Ma").Value = sCrt.MaSach

        End If

        If (sCrt.TenSach <> "") Then
            strDKTenSach = "TenSach like ?"
            cmd.Parameters.Add("@Ten", OleDbType.WChar)
            cmd.Parameters("@Ten").Value = "%" + sCrt.TenSach + "%"
        End If


        If (sCrt.CheckTacGia) Then
            strDKTacGia = "TacGia like ?"
            cmd.Parameters.Add("@TacGia", OleDbType.WChar)
            cmd.Parameters("@TacGia").Value = "%" + sCrt.TacGia + "%"

        End If

        If (sCrt.CheckMaTheLoai) Then
            strDKMaTheLoai = "MaTheLoai = ? "
            cmd.Parameters.Add("@MaTheLoai", OleDbType.Integer)
            cmd.Parameters("@MaTheLoai").Value = sCrt.MaTheLoai
        End If


        If (sCrt.CheckMaNhanVien) Then
            strDKMaNhanVien = "MaNhanVien = ? "
            cmd.Parameters.Add("@MaNhanVien", OleDbType.Integer)
            cmd.Parameters("@MaNhanVien").Value = sCrt.MaNhanVien

        End If

        If (sCrt.CheckMaTinhTrang) Then
            strDKMaTinhTrang = "MaTinhTrang = ? "
            cmd.Parameters.Add("@MaTinhTrang", OleDbType.Integer)
            cmd.Parameters("@MaTinhTrang").Value = sCrt.MaTinhTrang
        End If

        If (sCrt.CheckNhaXuatBan) Then
            strDKNhaXuatBan = "NhaXuatBan like ?"
            cmd.Parameters.Add("@NhaXuatBan", OleDbType.WChar)
            cmd.Parameters("@NhaXuatBan").Value = "%" + sCrt.NhaXuatBan + "%"
        End If

        If (sCrt.CheckNamXuatBan) Then
            strDKNamXuatBan = "NamXuatBan = ?"
            cmd.Parameters.Add("@NamXuatBan", OleDbType.Date)
            cmd.Parameters("@NamXuatBan").Value = sCrt.NamXuatBan
        End If

        If (sCrt.CheckNgayNhap) Then
            strDKNgayNhap = "NgayNhap = ?"
            cmd.Parameters.Add("@NgayNhap", OleDbType.Date)
            cmd.Parameters("@NgayNhap").Value = sCrt.NgayNhap
        End If

        If (sCrt.CheckTriGia) Then
            strDKTriGia = "TriGia between ? and ? "
            cmd.Parameters.Add("@TriGiaTu", OleDbType.Double)
            cmd.Parameters.Add("@TriGiaDen", OleDbType.Double)
            cmd.Parameters("@TriGiaTu").Value = sCrt.TriGiaTu
            cmd.Parameters("@TriGiaDen").Value = sCrt.TriGiaDen
        End If


        'If (sCrt.CheckKhoangCachXuatBan) Then
        '    strDKKhoangCachXuatBan = "KhoangCachXuatBan = ? "
        '    cmd.Parameters.Add("@KhoangCachXuatBan", OleDbType.Double)
        '    cmd.Parameters("@KhoangCachXuatBan").Value = sCrt.KhoangCachXuatBan
        'End If


        Dim strDKWhere As String = "Where"
        strDKWhere += " and " + strDKMaSach
        strDKWhere += " and " + strDKMaTheLoai
        strDKWhere += " and " + strDKMaNhanVien
        strDKWhere += " and " + strDKMaTinhTrang
        strDKWhere += " and " + strDKTenSach
        strDKWhere += " and " + strDKNhaXuatBan
        strDKWhere += " and " + strDKNamXuatBan
        strDKWhere += " and " + strDKTriGia
        strDKWhere += " and " + strDKNgayNhap
        'strDKWhere += " and " + strDKKhoangCachXuatBan
        strDKWhere += " and " + strDKTacGia

        Dim strSQL As String = "Select * From Sach"
        strSQL += strDKWhere
        strSQL += "Oder by TenSach"


        cmd.Connection = cn
        cmd.CommandText = strSQL
        Return cmd


    End Function

    '------------------ham  doc gia tri trong csdl cua Sach-------------

    Public Function Laysach() As List(Of SachDTO)

        Dim sql = "select MaSach, TenSach,MaTheLoai,TacGia,NhaXuatBan,NamXuatBan,NgayNhap,TriGia,MaNhanVien,MaTinhTrang from Sach "
        Dim con As OleDbConnection = DatabaseProvider.connectData() 'ket noi csdl
        Dim cmd As New OleDbCommand(sql, con) ' thuc hien ket noi
        Dim reader As OleDbDataReader = cmd.ExecuteReader() ' thuc thi 
        Dim danhsach As New List(Of SachDTO) 'khai báo danhsach de du lieu do vao nó
        While reader.Read()
            Dim nvDTO As New SachDTO() ' khai báo doi tuong nvDTO de gán nhung doi tuong trong csdl vao
            nvDTO.MaSach = reader("MaSach") 'gán manv vao
            nvDTO.TenSach = reader("TenSach")
            nvDTO.MaTheLoai = reader("MaTheLoai")
            nvDTO.TacGia = reader("TacGia")
            nvDTO.NhaXuatBan = reader("NhaXuatBan")
            nvDTO.NamXuatBan = reader("NamXuatBan")
            nvDTO.NgayNhap = reader("NgayNhap")
            nvDTO.TriGia = reader("TriGia")
            nvDTO.MaNhanVien = reader("MaNhanVien")

            nvDTO.MaTinhTrang = reader("MaTinhTrang")
            danhsach.Add(nvDTO) ' add doi tuong nvDTO vao danhsach
        End While

        con.Close() ' đóng kết nối
        Return danhsach
    End Function


    Public Function ThemSach(ByVal sachDTO As SachDTO) As Integer
        'khai báo câu sql them nhân viên
        Dim sql As String = "insert into Sach (TenSach,MaTheLoai,TacGia,NhaXuatBan,NamXuatBan,NgayNhap,TriGia,MaNhanVien,MaTinhTrang) values (@TenSach,@MaTheLoai,@TacGia,@NhaXuatBan,@NamXuatBan,@NgayNhap,@TriGia,@MaNhanVien,@KhoangCachXuatBan,@MaTinhTrang)"
        'mở kết nối csdl
        Dim con As OleDbConnection = DatabaseProvider.connectData()
        'tạo câu lệnh đẻ kết nối csdl
        Dim cmd As New OleDbCommand(sql, con)
        Dim para As OleDbParameter
        'điền giá trị 
        'hjx ! sao khai bao moi , hen gi ko co du~ lieu !
        'Dim nvDTO As New NhanVienDTO()

        para = cmd.Parameters.Add("@TenSach", OleDbType.VarChar)
        para.Value = sachDTO.TenSach
        para = cmd.Parameters.Add("@MaTheLoai", OleDbType.Integer)
        para.Value = sachDTO.MaTheLoai
        para = cmd.Parameters.Add("@TacGia", OleDbType.VarChar)
        para.Value = sachDTO.TacGia
        para = cmd.Parameters.Add("@NhaXuatBan", OleDbType.VarChar)
        para.Value = sachDTO.NhaXuatBan
        para = cmd.Parameters.Add("@NamXuatBan", OleDbType.Date)
        para.Value = sachDTO.NamXuatBan
        para = cmd.Parameters.Add("@NgayNhap", OleDbType.Date)
        para.Value = sachDTO.NgayNhap
        para = cmd.Parameters.Add("@TriGia", OleDbType.Integer)
        para.Value = sachDTO.TriGia
        para = cmd.Parameters.Add("@MaNhanVien", OleDbType.VarChar)
        para.Value = sachDTO.MaNhanVien
        'para = cmd.Parameters.Add("@KhoangCachXuatBan", OleDbType.VarChar)
        'para.Value = sachDTO.KhoangCachXuatBan
        para = cmd.Parameters.Add("@MaTinhTrang", OleDbType.VarChar)
        para.Value = sachDTO.MaTinhTrang
        'thuc thi cau sql
        Dim i As Integer = cmd.ExecuteNonQuery()
        'dong ket noi 
        con.Close()
        If (i > 0) Then
            Return 1 'them thanh công
        Else
            Return 0 'them that bai
        End If

    End Function

    Public Function updatesach(ByVal sachDTO As SachDTO)
        'khai báo câu sql them nhân viên
        Dim sql As String = " update NhanVien set TenSach=@TenSach,MaTheLoai@MaTheLoai,TacGia=@TacGia,NhaXuatBan=@NhaXuatBan,NamXuatBan=@NamXuatBan,NgayNhap=@NgayNhap,TriGia=@TriGia,MaNhanVien=@MaNhanVien,MaTinhTrang=@MaTinhTrang)"
        'mở kết nối csdl
        Dim con As OleDbConnection = DatabaseProvider.connectData()
        'tạo câu lệnh đẻ kết nối csdl
        Dim cmd As New OleDbCommand(sql, con)
        Dim para As OleDbParameter
        'điền giá trị 
        'para = cmd.Parameters.Add("MaNV", OleDbType.VarChar)
        'para.Value = nvDTO.MaNV

        para = cmd.Parameters.Add("@TenSach", OleDbType.VarChar)
        para.Value = sachDTO.TenSach
        para = cmd.Parameters.Add("@MaTheLoai", OleDbType.Integer)
        para.Value = sachDTO.MaTheLoai
        para = cmd.Parameters.Add("@TacGia", OleDbType.VarChar)
        para.Value = sachDTO.TacGia
        para = cmd.Parameters.Add("@NhaXuatBan", OleDbType.VarChar)
        para.Value = sachDTO.NhaXuatBan
        para = cmd.Parameters.Add("@NamXuatBan", OleDbType.Date)
        para.Value = sachDTO.NamXuatBan
        para = cmd.Parameters.Add("@NgayNhap", OleDbType.Date)
        para.Value = sachDTO.NgayNhap
        para = cmd.Parameters.Add("@TriGia", OleDbType.Integer)
        para.Value = sachDTO.TriGia
        para = cmd.Parameters.Add("@MaNhanVien", OleDbType.VarChar)
        para.Value = sachDTO.MaNhanVien
        'para = cmd.Parameters.Add("@KhoangCachXuatBan", OleDbType.VarChar)
        'para.Value = sachDTO.KhoangCachXuatBan
        para = cmd.Parameters.Add("@MaTinhTrang", OleDbType.VarChar)
        para.Value = sachDTO.MaTinhTrang
        'thuc thi cau sql
        Dim i As Integer = cmd.ExecuteNonQuery()
        'dong ket noi 
        con.Close()
        If (i >= 0) Then
            Return 1 'cap nhat  thanh công
        Else
            Return 0 'cap nhat that bai
        End If

    End Function
   
    Public Function xoasach(ByVal Masach As Int32) As Integer
        'khai báo câu sql them nhân viên
        Dim sql As String = "delete from Sach where MaSach=" + Masach.ToString() 'tìm manv giong voi ma nhan vien minh truyen vao -->delect nó 
        'mở kết nối csdl
        Dim con As OleDbConnection = DatabaseProvider.connectData()
        'tạo câu lệnh đẻ kết nối csdl
        Dim cmd As New OleDbCommand(sql, con)

        Dim i As Integer = cmd.ExecuteNonQuery()
        'dong ket noi 
        con.Close()
        If (i >= 0) Then
            Return 1 'xoa thanh cong  thanh công
        Else
            Return 0 'xoa  that bai
        End If
    End Function
End Class
